﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ExternalEntityLookups]
(
	@EntityList nvarchar(255) = '*'
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @EXTVIEW nvarchar(max)
	DECLARE @COMBINEDVIEWS nvarchar(max)
	DECLARE @ENTITYTYPE nvarchar(100)
	DECLARE @FETCHEXTVIEWS nvarchar(max)
	DECLARE @WHERECLAUSE nvarchar(255)
	IF (@EntityList='*') SET @WHERECLAUSE = ''
	ELSE SET @WHERECLAUSE = ' WHERE (EntityType IN (' + @EntityList + '))'
	SET @FETCHEXTVIEWS = 
	'DECLARE ExternalViewsCursor cursor for
	SELECT sqlraw, EntityType FROM
	(SELECT
		SUBSTRING(RafikiNOSQL,CHARINDEX(''{'',RafikiNOSQL)+1,CHARINDEX(''}'',RafikiNOSQL)-CHARINDEX(''{'',RafikiNOSQL)-1)
		as extview,
		EntityType
	 from DefinedEntityTypes' + @WHERECLAUSE + 
	 '
	 ) AS X
	 JOIN DefinedExternalViews
		ON x.ExtView = DefinedExternalViews.ViewName'
	EXECUTE(@FETCHEXTVIEWS)
	
	SET @COMBINEDVIEWS = 'SELECT * FROM ('
	Open ExternalViewsCursor
	Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @ENTITYTYPE
	IF @@FETCH_STATUS = 0 BEGIN
		WHILE @@FETCH_STATUS = 0 BEGIN
			IF (CHARINDEX('LOOKUPNAME',@EXTVIEW)>0)
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT LOOKUPNAME, ENTITYID, ENTITYNAME, '''''+@ENTITYTYPE+ ''''' AS ENTITYTYPE, NULL AS ENTITYSUBTYPE, ''''LOOKUPNAME'''' as ATTRIBUTENAME FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @ENTITYTYPE
		END

		SET @COMBINEDVIEWS = SUBSTRING(@COMBINEDVIEWS,0,LEN(@COMBINEDVIEWS)-6) + ')'
		DECLARE @TSQL nvarchar(max)
		SELECT @TSQL = 'SELECT * FROM OPENQUERY(PSPRODSERVER,''' + @COMBINEDVIEWS + ')'')'
		EXEC(@TSQL)
	END
	CLose ExternalViewsCursor
	DEALLOCATE ExternalViewsCursor

END